﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Objects;
using System.Threading;

namespace XBoshiGFS
{



    class ImportWTKJData
    {
        XBoshiGFSModelContainer context = new XBoshiGFSModelContainer();

        public String ConnectionString {get;set ;}

        public bool IsFinished { get; set; }

        public bool IsClearData { get; set; }


        OleDbConnection objConnection ;


        private String currentStep = "";


        public void ImportStation() {
            objConnection.Open();  //打开连接 

            OleDbCommand sqlcmd = new OleDbCommand(@"select count(*) from zhan_tab ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   
            reader.Read();
            long RecordCOunt = (long)reader.GetInt32(0);
            reader.Close();



            sqlcmd = new OleDbCommand(@"select * from zhan_tab ", objConnection);  //sql语句   
             reader = sqlcmd.ExecuteReader();              //执行查询   
           
           while(reader.Read()){ //这个read调用很重要！不写的话运行时将提示找不到数据   

               String strID = reader["zhan_bih"].ToString();
               String strCode = reader["zhan_bih"].ToString();
               String strName = (String)reader["zhan_mic"];
               String strPhone = ""; //(String)reader["strPhone"];
               String strManager = "";// (String)reader["strManager"];
               String strOther = (String)reader["zhan_bez"];

               StationSet station = new StationSet(){
                Id=strID ,strCode =strCode ,strManager =strManager ,strName =strName ,strOther =strOther 
               };
               context.AddToStationSet(station);

           }
           context.SaveChanges() ; 
           objConnection.Close();
           reader.Close();  
        }

        public void ImportFeeStard()
        {
            objConnection.Open();  //打开连接   

          String   strSQl = "select a.shuj_bih,a.shuj_mic,a.shuj_jie,b.gouc_jig as mnyUnitPrice_N ,c.gouc_jig as mnyUnitPrice_B,d.gouc_jig as mnyUnitPrice_G"
                + " from  shuj_tab a,gouc_tab b,gouc_tab c,gouc_tab d "
                + " where b.shuj_bih=a.shuj_bih "
                + "  and c.shuj_bih=a.shuj_bih"
                + "  and d.shuj_bih=a.shuj_bih"
                + "  and b. gouc_bih like '%01'"
                + "  and c. gouc_bih like '%02'"
                + "  and d. gouc_bih like '%03'";

          OleDbCommand sqlcmd = new OleDbCommand(@strSQl, objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["shuj_bih"].ToString();
                    String strCode = reader["shuj_bih"].ToString();
                    String strName = reader["shuj_mic"].ToString();
                    Decimal PriceNational = 0;// Decimal.Parse(reader["mnyUnitPrice_N"].ToString());
                    Decimal PriceBase = 0;// Decimal.Parse(reader["mnyUnitPrice_B"].ToString());
                    Decimal PriceGroup = 0;// Decimal.Parse(reader["mnyUnitPrice_G"].ToString());
                    Decimal decPriceAll = Decimal.Parse(reader["shuj_jie"].ToString());
                    String strZhanID = (from p in context.StationSet select p).FirstOrDefault().Id ;

                    FeeStandardSet fee = new FeeStandardSet();
                    context.AddToFeeStandardSet(fee);

                    fee.Id = strID;
                    fee.strCode = strCode;
                    fee.PriceAll = decPriceAll;
                    fee.PriceNational  = PriceNational;
                    fee.PriceBase = PriceBase;
                    fee.PriceGroup = PriceGroup;
                    fee.strName = strName;
                    //segment.StationSet = (from p in context.StationSet where p.strCode == strZhanID select p).FirstOrDefault();
                    fee.Station_Id  = strZhanID;
                    context.SaveChanges();
                    fee = null;
                }
                catch (Exception ex)
                {
                }
            }

            objConnection.Close();
            reader.Close();
        }
        public void ImportSegment()
        {
            objConnection.Open();  //打开连接   
            String strSQL = "select  b.duan_bih,a.ganz_mic+b.duan_mic as ganz_mic,'' as ganz_bez"
                + "  from ganz_tab a,duan_tab b"
                + "  where a.ganz_bih=b.ganz_bih";
            OleDbCommand sqlcmd = new OleDbCommand(@strSQL, objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["duan_bih"].ToString();
                    String strCode = reader["duan_bih"].ToString();
                    String strName = reader["ganz_mic"].ToString();
                    String strPhone = "";// reader["strPhone"].ToString();
                    String strManager = "";// reader["strManager"].ToString();
                    String strOther = reader["ganz_bez"].ToString();
                    String strZhanID = (from p in context.StationSet select p ).FirstOrDefault().Id ;

                    SegmentCourseSet segment = new SegmentCourseSet();
                    context.AddToSegmentCourseSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                    segment.strManager = strManager;
                    segment.strName = strName;
                    segment.strOther = strOther;
                    //segment.StationSet = (from p in context.StationSet where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.StationId  = strZhanID;
                    context.SaveChanges();
                    context.SegmentCourseSet.Detach(segment);   
                    segment = null;
 
                }catch (Exception ex){
                    currentStep = ex.InnerException.ToString() ;
                
                }
            }

            objConnection.Close();
            reader.Close();
        }

        public void ImportWaterCourse()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from douq_tab ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try{
                    String strID = reader["douq_bih"].ToString();
                    String strCode = reader["douq_bih"].ToString();
                    String strName = reader["douq_mic"].ToString();
                    String strPhone = "";// reader["strPhone"].ToString();
                    String strManager = "";// reader["strManager"].ToString();
                String strOther = reader["douq_bez"].ToString();
                String strZhanID = reader["duan_bih"].ToString();


                WaterCourseSet segment = new WaterCourseSet();
                context.AddToWaterCourseSet(segment);

                segment.Id = strID;
                segment.strCode = strCode;
                segment.strManager = strManager;
                segment.strName = strName;
                segment.strOther = strOther;
            //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                segment.SegmentCourseId = strZhanID; 
                context.SaveChanges();
                context.WaterCourseSet.Detach(segment);   
                segment = null;

                 }catch (Exception ex){
                     currentStep = ex.InnerException.ToString();   
                }
            }

            objConnection.Close();
            reader.Close();
        }

        public void ImportVillage()
        {
            objConnection.Open();  //打开连接   
          String   strSQl = "select distinct c.*,a.douq_bih  "
                + " from guax_tab a, nonh_tab b,cunz_tab c, zubi_tab d"
                + " where a.nonh_bih=b.nonh_bih"
                + " and c.cunz_bih=d.cunz_bih"
                + " and d.zubi_bih=b.zubi_bih";


          OleDbCommand sqlcmd = new OleDbCommand(@strSQl, objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
    
                
                
                try
                {
                    String strID = reader["cunz_bih"].ToString();
                    VillageSet vs = (from p in context.VillageSet where p.Id == strID select p).FirstOrDefault();
                    if (vs != null) {
                        continue ;
                    }
                    String strCode = reader["cunz_bih"].ToString();
                    String strName = reader["cunz_mic"].ToString();
                    String strPhone = "";// reader["strPhone"].ToString();
                    String strManager = "";// reader["strManager"].ToString();
                    String strOther = reader["cunz_bez"].ToString();
                    String strDouID = reader["douq_bih"].ToString();
                    String strZhanID = (from p in context.StationSet select p).FirstOrDefault().Id;

                    VillageSet segment = new VillageSet();
                    context.AddToVillageSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                  //  segment.strManager = strManager;
                    segment.strName = strName;
                   // segment.strOther = strOther;
                    //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.WaterCourseId = strDouID;
                    segment.StationId = strZhanID;
                    context.SaveChanges();
                    context.VillageSet.Detach(segment);
                    segment = null;
                   }
                catch (Exception ex)
                {

                    currentStep = ex.InnerException.ToString();
                }
            }

            objConnection.Close();
            reader.Close();
        }


        public void ImportGroup()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from zubi_tab ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["zubi_bih"].ToString();
                    String strCode = reader["zubi_bih"].ToString();
                    String strName = reader["zubi_mic"].ToString();
                    String strPhone = "";// reader["strPhone"].ToString();
                    String strManager = "";// reader["strManager"].ToString();
                    String strOther = reader["zubi_bez"].ToString();
                    String strDouID = reader["cunz_bih"].ToString();
                    String strZhanID = "";// reader["IDZhan"].ToString();

                    GroupSet segment = new GroupSet();
                    context.AddToGroupSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                    segment.strManager = strManager;
                    segment.strName = strName;
                    segment.strOther = strOther;
                    //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.VillageId  = strDouID;
                    context.SaveChanges();
                    context.GroupSet.Detach(segment);
                    segment = null;
                }
                catch (Exception ex)
                {
                    currentStep = ex.InnerException.ToString();

                }
            }

            objConnection.Close();
            reader.Close();
        }

        public void ImportPeople()
        {
            objConnection.Open();  //打开连接   

            OleDbCommand sqlcmd = new OleDbCommand(@"select count(*) from nonh_tab ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   
            reader.Read();
            long RecordCOunt = (long)reader.GetInt32(0);
            reader.Close();


            sqlcmd = new OleDbCommand(@"select * from nonh_tab ", objConnection);  //sql语句   
            reader = sqlcmd.ExecuteReader();              //执行查询   
            var j=0;
            var k = 0;
             
            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = Guid.NewGuid().ToString();
                    String strCode = reader["nonh_bih"].ToString();
                    String strName = reader["nonh_mic"].ToString();
                    //String strPhone = reader["strPhone"].ToString();
                    //String strManager = reader["strManager"].ToString();
                    String strOther = reader["nonh_bez"].ToString();
                    String strAddr = "";// reader["strAddr"].ToString();

                    String strZuID = reader["zubi_bih"].ToString();
                    var a = (from p in context.GroupSet where p.Id == strZuID select p);
                    String strDouID = (from p in context.GroupSet where p.Id ==strZuID select p).FirstOrDefault().VillageSet.WaterCourseId ;

                    PeopleSet segment = new PeopleSet();
                    context.AddToPeopleSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                    //  segment.strManager = strManager;
                    segment.strName = strName;
                    segment.OrginID = reader["nonh_bih"].ToString();
                    // segment.strOther = strOther;
                    //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.GroupId = strZuID;
                    segment.WaterCourseId = strDouID;
                    if (k >= -1)
                    {
                        context.SaveChanges();

                        k = 0;
                        currentStep = String.Format("用水户数据导入完成{0}000条/{1}", j, RecordCOunt);
                        j = j + 1;
                        Thread.Sleep(0);  
                    }
                    else {
                        k = k + 1;
                    
                    }
                    context.Detach (segment);
                    //context.PeopleSet.Detach(segment);
                    segment = null;
                }
                catch (Exception ex)
                {
                     currentStep = ex.Message ;//  .InnerException.ToString();
                }

            }
            context.SaveChanges();
            objConnection.Close();
            reader.Close();
        }

        /// <summary>
        /// 导入用水记录
        /// </summary>
        public void ImportWaterUsage()
        {
            objConnection.Open();  //打开连接   

            OleDbCommand sqlcmd = new OleDbCommand(@"select count(*) from yons_tab ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   
            reader.Read();
            long RecordCOunt = (long)reader.GetInt32(0);
            reader.Close();



            sqlcmd = new OleDbCommand(@"  select * from yons_tab a where  a. guag_shl<>0 order by  nonh_bih ", objConnection);  //sql语句   
              reader = sqlcmd.ExecuteReader();              //执行查询   

            var k = 0;
            var j = 0;
            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = Guid.NewGuid().ToString();
                    //确定用水人
                    String strCode = reader["nonh_bih"].ToString();
                  //  String strZuID = reader["IDZu"].ToString();
                    PeopleSet  people = (from p in context.PeopleSet where  p.OrginID == strCode select p).FirstOrDefault();



                    //确定灌季
                    String strYear = reader["guaj_nid"].ToString();
                    String strGJ = strYear + "年" +reader["guaj_mic"].ToString();
                    
                    WaterSeasonSet season = (from p in context.WaterSeasonSet where p.strYear == strYear & p.strName  == strGJ select p).FirstOrDefault();



                    //确定费用标准
                    String strFeeStand = reader["shuj_bih"].ToString();




                    //用水信息
                    DateTime strBeginTime = DateTime.Parse(reader["guag_riq"].ToString()); //开始用水时间
                    decimal strUseLong = Decimal.Parse(reader["guag_shc"].ToString());//用水时长
                    decimal strFlowRate = Decimal.Parse(reader["guag_lil"].ToString());
                    decimal strUseArea = Decimal.Parse(reader["shig_mij"].ToString());
                    decimal strWaterBill = Decimal.Parse(reader["shuf_jie"].ToString());

                    //开票标志
                    String strRecState = reader["kaip_riq"].ToString();
                    bool isFeePrinLog = true;
                    if (String.IsNullOrWhiteSpace( strRecState)) { 
                        //已经开票
                        isFeePrinLog = false;
                    }

                    //收费标志

                    WaterUsageSet wus = new WaterUsageSet();
                    context.AddToWaterUsageSet(wus);

                    wus.Id = strID;
                    wus.PeopleSet = people;
                    wus.FeeStandardId = strFeeStand;
                    wus.WaterSeasonSet = season;
                    wus.UseArea = strUseArea;
                    wus.UseLong = strUseLong;
                    wus.WaterBill = strWaterBill;
                    wus.BeginTime = strBeginTime;
                    wus.FlowRate = strFlowRate;
                    wus.WaterCap = Decimal.Parse(reader["guag_shl"].ToString()); //strFlowRate * strUseLong*60/1000;

                    //发票打印
                    if (isFeePrinLog) { 
                        FeePrintLogSet fls=new FeePrintLogSet ();
                        fls.Id=Guid.NewGuid().ToString ();
                        fls.WaterUsageId = strID;
                        fls.PrintTime =DateTime.Parse(reader["kaip_riq"].ToString())  ;
                        context.AddToFeePrintLogSet(fls);  
                    }

                    //收费数据



                    if (k >= -1)
                    {
                        context.SaveChanges();

                        currentStep = String.Format("用水数据导入完成{0}条/{1}", j, RecordCOunt);
                        j = j + 1;
                        Thread.Sleep(0);
                        k = 0;
                    }
                    else
                    {
                        k = k + 1;

                    }

                    context.Detach(wus);
                    wus = null;
                }
                catch (Exception ex)
                {
                    currentStep = "" + ex.Message; 

                }
    
            }
            context.SaveChanges();
            objConnection.Close();
            reader.Close();
        }


        /// <summary>
        /// 导入灌季
        /// </summary>
        public void ImportWaterSeason()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select distinct guaj_nid as sintGJN, guaj_mic as sintGJ from yons_tab ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = Guid.NewGuid().ToString() ;
                    String strYear = reader["sintGJN"].ToString();
                    String strName = reader["sintGJ"].ToString();
                    String strSeason ="";
                    switch (strName.Substring(0,2) )
                    {
                        case "春灌":
                            strSeason = "1";
                            break;
                        case "夏灌":
                            strSeason = "2";
                            break;
                        case "秋灌":
                            strSeason = "3";
                            break;
                        case "冬灌":
                            strSeason = "4";
                            break;
                        default:
                            strSeason = "0";
                            break;
                    }

                    String strFeeStandardId = (from p in context.FeeStandardSet select p).FirstOrDefault().Id;//   "1"; // reader["IDType"].ToString();//默认库水
                    switch (strSeason)
                    {
                        case "1":
                            strName = strYear + "年" + strName;
                            break;
                        case "2":
                            strName = strYear + "年" + strName;
                            break;
                        case "3":
                            strName = strYear + "年" + strName;
                            break;
                        case "4":
                            strName = strYear + "年" + strName;
                            break;
                        default :
                            strName = strYear + "年" + strName;
                            break;
                    }



                    WaterSeasonSet season = new WaterSeasonSet();
                    
                    context.AddToWaterSeasonSet(season);
                    

                    season.Id = strID;
                    season.strYear = strYear;
                    season.strSeason = strSeason; 
                    season.strName = strName;
                    season.FeeStandardSetId = strFeeStandardId;
                    season.strState = "灌季中"; 
                    context.SaveChanges();
    
                    season = null;
                }
                catch (Exception ex)
                {


                }
            }

            objConnection.Close();
            reader.Close();
        }


        public void buidConnection() {

            objConnection = new OleDbConnection(ConnectionString);
        }

        public void ClearDataBase(){
         }


        public void BeginImport(){

            buidConnection();

            if (IsClearData)
            {
                ClearDataBase();
            }

            currentStep =String.Format("{0}开始导入数据：",DateTime.Now);

            ImportStation();
            currentStep = "管理站数据导入完成！";

            ImportFeeStard();
            currentStep = "收费标准数据导入完成";

            ImportWaterSeason();
            currentStep = "灌季数据导入完成";


            ImportSegment();
            currentStep = "支渠数据导入完成";

            ImportWaterCourse();
            currentStep = "斗渠数据导入完成";


            ImportVillage();
            currentStep = "村数据导入完成";

            ImportGroup();
            currentStep = "组数据导入完成";

            ImportPeople();
            currentStep = "用水户数据导入完成"; 

            ImportWaterUsage();
            currentStep = "用水数据导入完成"; 


            IsFinished = true;
        }

        public String  GetCurrentStep() {

            return currentStep;
        }
    }
}
